﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace InnovTech.Util.Office
{
    public class Excel03Util
    {
        public static void Wirte(string excelFile, DataSet data, bool withHeader)
        {
            DataSet ds = new DataSet();

            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = "Data Source=" + excelFile +
                ";Provider=Microsoft.Jet.OLEDB.4.0;";

            if (withHeader)
                con.ConnectionString += @"Extended Properties=""Excel 8.0;HDR=Yes""";
            else
                con.ConnectionString += @"Extended Properties=""Excel 8.0;HDR=No""";

            con.Open();

            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", con);

            int colCount = data.Tables[0].Columns.Count;

            string insert = "INSERT INTO [Sheet1$] (";
            for (int i = 0; i < colCount; i++)
            {
                insert += data.Tables[0].Columns[i].ColumnName;
                insert += ",";
            }
            insert = insert.Substring(0, insert.Length - 1);
            insert += ") VALUES (";
            for (int i = 0; i < colCount; i++)
            {
                insert += "?,";
            }
            insert = insert.Substring(0, insert.Length - 1);
            insert += ")";

            adapter.InsertCommand = new OleDbCommand(insert, con);
            OleDbParameter param = new OleDbParameter("@en", OleDbType.VarChar, 255, "en");
            param.IsNullable = true;
            adapter.InsertCommand.Parameters.Add(param);
            DataTable table = data.Tables[0];
            //adapter.Update(data, "table");

            OleDbCommand com = new OleDbCommand("insert into [sheet1$] (en) values ('a')", con);
            com.ExecuteNonQuery();

            con.Close();
        }

        static string testInsert = @"INSERT INTO [Sheet1$] IN 'D:\test.xls' 'Excel 8.0;' SELECT * FROM [table]";
    }
}
